#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL数据库设计文档生成工具
根据MySQL的数据库表创建SQL文件，生成Word格式的数据库设计说明文档
"""
import re
import os
from datetime import datetime
from docx import Document
from docx.shared import Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT


def parse_mysql_sql(sql_file_path):
    """
    解析MySQL SQL文件，提取表结构信息
    :param sql_file_path: MySQL SQL文件路径
    :return: 包含所有表结构信息的字典
    """
    tables_info = {}
    
    try:
        # 读取SQL文件
        with open(sql_file_path, 'r', encoding='utf-8') as f:
            content = f.read()
        
        # 按语句分割SQL
        statements = split_sql_statements(content)
        
        for statement in statements:
            statement = statement.strip()
            if not statement or not statement.upper().startswith('CREATE TABLE'):
                continue
            
            # 提取表信息
            table_info = extract_table_info(statement)
            if table_info:
                tables_info[table_info['name']] = table_info
        
        return tables_info
        
    except Exception as e:
        print(f"解析SQL文件时发生错误: {str(e)}")
        return {}


def split_sql_statements(content):
    """
    将SQL内容按语句分割
    """
    # 首先去除行内注释
    content = re.sub(r'--.*?$', '', content, flags=re.MULTILINE)
    
    # 分割SQL语句（以分号为分隔符）
    statements = []
    current_statement = ''
    in_string = False
    in_comment = False
    
    for char in content:
        if char == '\'' and (not current_statement or current_statement[-1] != '\\'):
            in_string = not in_string
        elif char == '/' and len(current_statement) > 0 and current_statement[-1] == '*' and not in_string:
            in_comment = True
        elif char == '*' and len(current_statement) > 0 and current_statement[-1] == '/' and not in_string and in_comment:
            in_comment = False
            current_statement = current_statement[:-1]  # 移除 '*'
            continue
        
        if not in_comment:
            current_statement += char
            if char == ';' and not in_string:
                statements.append(current_statement)
                current_statement = ''
    
    # 处理最后一个没有分号结束的语句
    if current_statement.strip():
        statements.append(current_statement)
    
    return statements


def extract_table_info(create_table_statement):
    """
    从CREATE TABLE语句中提取表结构信息
    """
    table_info = {}
    
    # 提取表名
    table_match = re.search(r'CREATE TABLE\s+`?([^`\(]+)`?\s*\(', create_table_statement, re.IGNORECASE)
    if not table_match:
        return None
    
    table_name = table_match.group(1).strip()
    table_info['name'] = table_name
    
    # 提取表注释
    comment_match = re.search(r'COMMENT\s*=\s*\'([^\']+)\'', create_table_statement, re.IGNORECASE)
    table_info['comment'] = comment_match.group(1) if comment_match else ''
    
    # 提取表结构部分
    structure_pattern = r'\((?:(?:"[^"\\]*(?:\\.[^"\\]*)*"|`[^`\\]*(?:\\.[^`\\]*)*`|\([^()]*\)|[^()"`])*)\)'  # 处理括号嵌套的正则表达式
    structure_match = re.search(structure_pattern, create_table_statement, re.DOTALL)
    if not structure_match:
        return table_info
    
    structure = structure_match.group(0)[1:-1].strip()  # 去掉外层括号
    
    # 解析列信息
    columns = []
    bracket_count = 0
    current_col = ''
    in_quotes = False
    quote_char = None
    
    for char in structure:
        # 跟踪引号状态
        if char in ['"', "'", '`'] and (not quote_char or char == quote_char) and (not current_col or current_col[-1] != '\\'):
            in_quotes = not in_quotes
            quote_char = char if in_quotes else None
        
        # 跟踪括号状态
        if char == '(' and not in_quotes:
            bracket_count += 1
        elif char == ')' and not in_quotes:
            bracket_count -= 1
        
        # 分割列定义
        if char == ',' and bracket_count == 0 and not in_quotes:
            columns.append(current_col.strip())
            current_col = ''
        else:
            current_col += char
    
    # 添加最后一个列定义
    if current_col.strip():
        columns.append(current_col.strip())
    
    # 处理每列信息
    table_info['columns'] = []
    primary_keys = []
    
    for col_def in columns:
        col_def = col_def.strip()
        if not col_def:
            continue
        
        # 跳过表级约束
        if col_def.upper().startswith('PRIMARY KEY') or col_def.upper().startswith('UNIQUE') or col_def.upper().startswith('KEY'):
            # 提取主键信息
            if col_def.upper().startswith('PRIMARY KEY'):
                pk_match = re.search(r'PRIMARY KEY\s*\(([^)]+)\)', col_def, re.IGNORECASE)
                if pk_match:
                    pk_columns = pk_match.group(1).split(',')
                    for pk_col in pk_columns:
                        pk_col = pk_col.strip().strip('`"')
                        primary_keys.append(pk_col)
            continue
        
        # 解析列定义
        col_info = parse_column_definition(col_def)
        if col_info:
            table_info['columns'].append(col_info)
    
    # 更新主键信息
    for col in table_info['columns']:
        if col['name'] in primary_keys:
            col['is_primary_key'] = True
    
    return table_info


def parse_column_definition(col_def):
    """
    解析列定义，提取列信息
    """
    col_info = {
        'name': '',
        'type': '',
        'is_nullable': True,
        'default_value': None,
        'comment': '',
        'is_primary_key': False,
        'is_auto_increment': False
    }
    
    # 提取列名
    name_match = re.search(r'^\s*`?([^`\s]+)`?', col_def)
    if not name_match:
        return None
    
    col_info['name'] = name_match.group(1)
    
    # 提取列类型
    type_match = re.search(r'`?[^`\s]+`?\s+([^\s,]+(?:\s*\([^)]*\))?)', col_def, re.IGNORECASE)
    if type_match:
        col_info['type'] = type_match.group(1).strip()
    
    # 检查是否NOT NULL
    if re.search(r'NOT\s+NULL', col_def, re.IGNORECASE):
        col_info['is_nullable'] = False
    
    # 检查是否AUTO_INCREMENT
    if re.search(r'AUTO_INCREMENT', col_def, re.IGNORECASE):
        col_info['is_auto_increment'] = True
    
    # 提取默认值
    default_match = re.search(r'DEFAULT\s+([^,\s]+)', col_def, re.IGNORECASE)
    if default_match:
        default_value = default_match.group(1)
        # 去除引号
        if (default_value.startswith("'") and default_value.endswith("'") or
            default_value.startswith('"') and default_value.endswith('"')):
            default_value = default_value[1:-1]
        col_info['default_value'] = default_value
    
    # 提取注释
    comment_match = re.search(r'COMMENT\s+([^,]+)', col_def, re.IGNORECASE)
    if comment_match:
        comment = comment_match.group(1)
        # 去除引号
        if (comment.startswith("'") and comment.endswith("'") or
            comment.startswith('"') and comment.endswith('"')):
            comment = comment[1:-1]
        col_info['comment'] = comment
    
    return col_info


def generate_word_document(tables_info, output_file_path):
    """
    生成Word文档
    :param tables_info: 表结构信息字典
    :param output_file_path: 输出Word文件路径
    """
    try:
        # 创建文档对象
        doc = Document()
        
        # 添加标题
        title = doc.add_heading('数据库设计说明文档', 0)
        title.alignment = WD_ALIGN_PARAGRAPH.CENTER
        
        # 添加文档信息
        doc.add_paragraph(f'生成时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}')
        doc.add_paragraph(f'数据库类型: MySQL')
        doc.add_paragraph(f'表数量: {len(tables_info)}')
        
        # 添加目录
        doc.add_page_break()
        doc.add_heading('目录', level=1)
        for table_name in sorted(tables_info.keys()):
            table_info = tables_info[table_name]
            table_title = f"{table_name} {table_info['comment']}" if table_info['comment'] else table_name
            doc.add_paragraph(table_title, style='List Bullet')
        
        # 为每个表生成详细信息
        for table_name in sorted(tables_info.keys()):
            doc.add_page_break()
            table_info = tables_info[table_name]
            
            # 添加表标题
            table_heading = f"表名: {table_name}"
            if table_info['comment']:
                table_heading += f" ({table_info['comment']})"
            doc.add_heading(table_heading, level=1)
            
            # 添加表结构表格
            table = doc.add_table(rows=1, cols=7)
            table.alignment = WD_TABLE_ALIGNMENT.CENTER
            table.style = 'Table Grid'
            
            # 设置表头
            hdr_cells = table.rows[0].cells
            hdr_cells[0].text = '列名'
            hdr_cells[1].text = '数据类型'
            hdr_cells[2].text = '是否可空'
            hdr_cells[3].text = '默认值'
            hdr_cells[4].text = '是否主键'
            hdr_cells[5].text = '是否自增'
            hdr_cells[6].text = '注释'
            
            # 填充表格内容
            for col in table_info['columns']:
                row_cells = table.add_row().cells
                row_cells[0].text = col['name']
                row_cells[1].text = col['type']
                row_cells[2].text = '是' if col['is_nullable'] else '否'
                row_cells[3].text = str(col['default_value']) if col['default_value'] is not None else ''
                row_cells[4].text = '是' if col['is_primary_key'] else '否'
                row_cells[5].text = '是' if col['is_auto_increment'] else '否'
                row_cells[6].text = col['comment']
        
        # 保存文档
        doc.save(output_file_path)
        print(f"数据库设计文档已成功生成: {output_file_path}")
        
    except Exception as e:
        print(f"生成Word文档时发生错误: {str(e)}")


def main():
    """
    主函数
    """
    # SQL文件路径
    sql_file_path = 'mysql.sql'
    # 输出Word文档路径
    output_file_path = f'database_design_{datetime.now().strftime("%Y%m%d_%H%M%S")}.docx'
    
    # 检查SQL文件是否存在
    if not os.path.exists(sql_file_path):
        print(f"错误：SQL文件 '{sql_file_path}' 不存在！")
        exit(1)
    
    print(f"开始解析SQL文件: {sql_file_path}")
    tables_info = parse_mysql_sql(sql_file_path)
    
    if not tables_info:
        print("未解析到任何表结构信息！")
        exit(1)
    
    print(f"成功解析到 {len(tables_info)} 个表的结构信息")
    print(f"开始生成数据库设计文档: {output_file_path}")
    generate_word_document(tables_info, output_file_path)


if __name__ == '__main__':
    main()